package org.boyan.cases;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.boyan.entity.query.*;
import org.boyan.entity.trans.Trans;
import org.boyan.utils.ExcelExportUtils;
import org.boyan.utils.ExcelUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class CaseBodyToExcel {

    public static void exportCaseBody(File file, List<Map<String, Object>> caseBodyList,Boolean editFlag) throws IOException {
        String fileName = file.getName();
        Workbook workbook = null;
        InputStream in = null;
        in = new FileInputStream(file);
        if (ExcelUtils.judegExcelEdition(fileName)) {
            workbook = new XSSFWorkbook(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        if(!editFlag){
            //复制几份sheet
            ExcelExportUtils.copySheet(workbook,3,caseBodyList.size()-1);
        }
        for (int i = 0; i < caseBodyList.size(); i++) {
            //取得Trans
            Trans trans = JSON.parseObject(JSON.toJSONString(caseBodyList.get(i).get("trans")), Trans.class);
            Sheet transSheet = workbook.getSheetAt(3 + (i * 3));
            writeTrans(transSheet,trans,editFlag);
            //修改sheet名
            workbook.setSheetName(3+(i*3),trans.getTransCode()+"-查询接口与逻辑");
            //获取支撑设计
            StrutDevise strutDevise = JSON.parseObject(JSON.toJSONString(caseBodyList.get(i).get("strut")), StrutDevise.class);
            Sheet strutSheet = workbook.getSheetAt(4 + (i * 3));
            writeStrut(strutSheet,strutDevise,editFlag);
            workbook.setSheetName(4+(i*3),trans.getTransCode()+"-支撑设计");
            //获取联机设计
            OnlineDeviseScheme onlineDeviseScheme = JSON.parseObject(JSON.toJSONString(caseBodyList.get(i).get("online")), OnlineDeviseScheme.class);
            Sheet onlineSheet = workbook.getSheetAt(5 + (i * 3));
            writeOnline(onlineSheet,onlineDeviseScheme,editFlag);
            workbook.setSheetName(5+(i*3),trans.getTransCode()+"-联机设计");
        }
        ExcelExportUtils.writeWorkbook(workbook,file);
        workbook.close();
        in.close();
    }

    private static void writeOnline(Sheet onlineSheet, OnlineDeviseScheme onlineDeviseScheme,Boolean editFlag) {
        String chooser=onlineDeviseScheme.getChooser();
        onlineSheet.getRow(0).getCell(1).setCellValue(chooser);
        int indexRow=getIndexRow(onlineSheet);
        List<IndexColumn> indexColumnList =onlineDeviseScheme.getIndexColumnList();
        //复制几行
        if(!editFlag) {
            ExcelExportUtils.insertRow(onlineSheet, indexRow + 1, 8, indexColumnList.size());
        }
        if("Hbase+Solr".equals(chooser)){
            onlineSheet.getRow(indexRow-2).getCell(1).setCellValue(onlineDeviseScheme.getIndexName());
            onlineSheet.getRow(indexRow-1).getCell(1).setCellValue(onlineDeviseScheme.getAmount());
            for(int i=0;i<indexColumnList.size();i++){
                onlineSheet.getRow(indexRow+i+1).getCell(0).setCellValue(indexColumnList.get(i).getColumnName());
                onlineSheet.getRow(indexRow+i+1).getCell(1).setCellValue(indexColumnList.get(i).getColumnType());
                onlineSheet.getRow(indexRow+i+1).getCell(3).setCellValue(indexColumnList.get(i).getColumnPurpose());
                boolean saveFlag=indexColumnList.get(i).isSaveFlag();
                if(saveFlag){
                    onlineSheet.getRow(indexRow+i+1).getCell(2).setCellValue("Y");
                }else{
                    onlineSheet.getRow(indexRow+i+1).getCell(2).setCellValue("N");
                }
            }
        }else{
            onlineSheet.getRow(indexRow-2).getCell(5).setCellValue(onlineDeviseScheme.getIndexName());
            onlineSheet.getRow(indexRow-1).getCell(5).setCellValue(onlineDeviseScheme.getAmount());
            for(int i=0;i<indexColumnList.size();i++){
                onlineSheet.getRow(indexRow+i+1).getCell(4).setCellValue(indexColumnList.get(i).getColumnName());
                onlineSheet.getRow(indexRow+i+1).getCell(5).setCellValue(indexColumnList.get(i).getColumnType());
                onlineSheet.getRow(indexRow+i+1).getCell(7).setCellValue(indexColumnList.get(i).getColumnPurpose());
                boolean saveFlag=indexColumnList.get(i).isSaveFlag();
                if(saveFlag){
                    onlineSheet.getRow(indexRow+i+1).getCell(6).setCellValue("Y");
                }else{
                    onlineSheet.getRow(indexRow+i+1).getCell(6).setCellValue("N");
                }
            }
        }

    }

    private static void writeStrut(Sheet strutSheet, StrutDevise strutDevise,Boolean editFlag) {
        strutSheet.getRow(1).getCell(4).setCellValue(strutDevise.getClusterTableName());
        strutSheet.getRow(1).getCell(5).setCellValue(strutDevise.getRowkeyRule());
        strutSheet.getRow(1).getCell(6).setCellValue(strutDevise.getDataVolume());
        strutSheet.getRow(1).getCell(7).setCellValue(strutDevise.getDataGrowth());
        int clusterTableRow=getClusterTableRow(strutSheet);
        List<ClusterTable> clusterTableList=strutDevise.getClusterTableList();
        //复制对应行
        if(!editFlag) {
            ExcelExportUtils.insertRow(strutSheet, clusterTableRow + 1, 8, clusterTableList.size());
        }
        for(int i=0;i<clusterTableList.size();i++){
            strutSheet.getRow(clusterTableRow+1+i).getCell(4).setCellValue(strutDevise.getClusterTableName());
            strutSheet.getRow(clusterTableRow+1+i).getCell(5).setCellValue(clusterTableList.get(i).getColumnName());
            strutSheet.getRow(clusterTableRow+1+i).getCell(6).setCellValue(clusterTableList.get(i).getColumnType());
            strutSheet.getRow(clusterTableRow+1+i).getCell(7).setCellValue(clusterTableList.get(i).getColumnCnMeaning());
        }
    }

    //将trans写入transSheet
    private static void writeTrans(Sheet sheet, Trans trans,Boolean editFlag) {
        sheet.getRow(0).getCell(1).setCellValue(trans.getTransName());
        sheet.getRow(1).getCell(1).setCellValue(trans.getTransCode());
        List<QueryInterface> input = trans.getInput();
        //获取输入行，并复制几行
        int inputRow = getInputRow(sheet);
        if(!editFlag) {
            ExcelExportUtils.insertRow(sheet, inputRow + 1, 6, input.size());
        }
        for (int i = 0; i < input.size(); i++) {
            sheet.getRow(inputRow + 1 + i).getCell(0).setCellValue(input.get(i).getEnName());
            sheet.getRow(inputRow + 1 + i).getCell(1).setCellValue(input.get(i).getCnName());
            sheet.getRow(inputRow + 1 + i).getCell(2).setCellValue(input.get(i).getDataType());
            sheet.getRow(inputRow + 1 + i).getCell(4).setCellValue(input.get(i).getQueryLogic());
            boolean inputFlag = input.get(i).isInputFlag();
            if (inputFlag) {
                sheet.getRow(inputRow + 1 + i).getCell(3).setCellValue("Y");
            } else {
                sheet.getRow(inputRow + 1 + i).getCell(3).setCellValue("N");
            }
        }
        List<QueryInterface> output = trans.getOutput();
        //获取输出行，并复制几行
        int outputRow = getOutputRow(sheet);
        if(!editFlag) {
            ExcelExportUtils.insertRow(sheet, outputRow + 1, 6, output.size());
        }
        for (int i = 0; i < output.size(); i++) {
            sheet.getRow(outputRow + 1 + i).getCell(0).setCellValue(output.get(i).getEnName());
            sheet.getRow(outputRow + 1 + i).getCell(1).setCellValue(output.get(i).getCnName());
            sheet.getRow(outputRow + 1 + i).getCell(2).setCellValue(output.get(i).getDataType());
            sheet.getRow(outputRow + 1 + i).getCell(4).setCellValue(output.get(i).getQueryLogic());
            boolean inputFlag = output.get(i).isInputFlag();
            if (inputFlag) {
                sheet.getRow(outputRow + 1 + i).getCell(3).setCellValue("Y");
            } else {
                sheet.getRow(outputRow + 1 + i).getCell(3).setCellValue("N");
            }
        }
        //表对象相关
        List<QueryInterface> arrayObject = trans.getArrayObject();
        int arrayRow = getArrayStartRow(sheet);
        if(!editFlag) {
            ExcelExportUtils.insertRow(sheet, arrayRow + 1, 6, arrayObject.size());
        }
        for (int i = 0; i < arrayObject.size(); i++) {
            sheet.getRow(arrayRow + 1 + i).getCell(0).setCellValue(arrayObject.get(i).getEnName());
            sheet.getRow(arrayRow + 1 + i).getCell(1).setCellValue(arrayObject.get(i).getCnName());
            sheet.getRow(arrayRow + 1 + i).getCell(2).setCellValue(arrayObject.get(i).getDataType());
            sheet.getRow(arrayRow + 1 + i).getCell(4).setCellValue(arrayObject.get(i).getQueryLogic());
            boolean inputFlag = arrayObject.get(i).isInputFlag();
            if (inputFlag) {
                sheet.getRow(arrayRow + 1 + i).getCell(3).setCellValue("Y");
            } else {
                sheet.getRow(arrayRow + 1 + i).getCell(3).setCellValue("N");
            }
        }
        //写入Sql
        int sqlRow = getSqlRow(sheet);
        String sqlStr = "查询SQL语句:" + trans.getQuerySql();
        sheet.getRow(sqlRow).getCell(0).setCellValue(sqlStr);
    }

    //获取输入项的起始row
    private static int getInputRow(Sheet sheet) {
        for (Row row : sheet) {
            if ("输入".equals(row.getCell(0).getStringCellValue())) {
                return row.getRowNum();
            }
        }
        return -1;
    }

    //获取输出项的起始row
    private static int getOutputRow(Sheet sheet) {
        for (Row row : sheet) {
            if ("输出".equals(row.getCell(0).getStringCellValue())) {
                return row.getRowNum();
            }
        }
        return -1;
    }

    //获取表对象的起始row
    private static int getArrayStartRow(Sheet sheet) {
        for (Row row : sheet) {
            if ("start".equalsIgnoreCase(row.getCell(4).getStringCellValue())) {
                return row.getRowNum();
            }
        }
        return -1;
    }

    //获取表对象的Sql行
    private static int getSqlRow(Sheet sheet) {
        for (Row row : sheet) {
            if(row.getCell(1)==null){
                continue;
            }
            if (row.getCell(0).getStringCellValue().startsWith("查询SQL语句")) {
                return row.getRowNum();
            }
        }
        return -1;
    }

    //获取支撑设计中的字段名的行
    private static int getClusterTableRow(Sheet sheet) {
        for (Row row : sheet) {
            if(row.getCell(5)==null){
                continue;
            }
            if ("字段名".equals(row.getCell(5).getStringCellValue())) {
                return row.getRowNum();
            }
        }
        return -1;
    }

    //获取练级设计中索引字段名的行
    private static int getIndexRow(Sheet sheet) {
        for (Row row : sheet) {
            if(row.getCell(0)==null){
                continue;
            }
            if ("索引字段名".equals(row.getCell(0).getStringCellValue())) {
                return row.getRowNum();
            }
        }
        return -1;
    }
}
